<?php
class Model_Resource_SiteResource_Section_Advert extends Zend_Db_Table
{
    protected $_name = 'advert';

    public function fetchAllWithPhones($sectionId, $realtorRating, $date, $phoneId, $realtor = null)
    {
        $select = $this->select();
        $select->from(array('main_table' => $this->_name))
            ->joinLeft(array('rel' => 'phone_advert_relation'),
                'rel.advert_id = main_table.id', array()
            )
            ->joinLeft(array('ph' => 'phone_entities'),
                'rel.phone_id = ph.id', array()
            )
            ->columns(array(
                'realtor'        => "if(SUM(ph.realtor) > 0, 1, 0)",
                'phones'         => "GROUP_CONCAT(ph.phone SEPARATOR ', ')",
                'rating_phones'  => "GROUP_CONCAT(CONCAT(ph.phone, '(', ph.count_advert, ')') SEPARATOR ', ')",
                'realtor_rating' => "CEIL(AVG(ph.count_advert))"
            ))
            ->where('main_table.section_id = ?', (int) $sectionId)

            ->having('realtor_rating <= ?', (int) $realtorRating)
            ->group('main_table.id')
            ->order('realtor_rating ASC')
        ;
        if ($phoneId) {
            $select->joinLeft(array('rel_f' => 'phone_advert_relation'),
                'rel_f.advert_id = main_table.id', array()
            );
            $select->where('rel_f.phone_id = ?', $phoneId);
        } else {
            if (null !== $realtor) {
                $select->where('ph.realtor = ?', (int) $realtor);
            }
            $select->where('main_table.created_at = ? OR main_table.last_repeat_at = ?', $date);
        }
        return $this->fetchAll($select);
    }
}

